use crate::base::get_ch_pool;

pub struct BizFeeBack {}
impl BizFeeBack {
    pub fn new() -> Self {
        Self {}
    }
    async fn collapsing(&self) -> anyhow::Result<()> {
        let sql = r#"insert into TMP_CM_BIZ_FEE_SUM_M_BACK(CUST_NUM,BELONG_ORG_NUM,CHARGE_TYPE_CD,FEE_PROD_ENCODE,CHAN_ID,ENTER_ACCT_CURR_CD,CASH_TRAN_IND,
        GL_NUM,STD_ACCT_SEQ_NUM,DATA_DT,ACTL_CHARGE_AMT,PREFR_FEE_AMT,FEE_PROD_NAME,CHARGE_TYPE_NAME,GL_NAME,ACTL_CHARGE_AMT_DISCNT_RMB,
        PREFR_FEE_AMT_DISCNT_RMB,ACTL_CHARGE_VAT,KHLX,JLID,CUID,ITID,UTID,ACID,DIVI.DPID,DIVI.EMID,DIVI.DSVL,SIGN)
        SELECT anyLast(CUST_NUM),anyLast(BELONG_ORG_NUM),anyLast(CHARGE_TYPE_CD),anyLast(FEE_PROD_ENCODE),anyLast(CHAN_ID),
        anyLast(ENTER_ACCT_CURR_CD),anyLast(CASH_TRAN_IND),anyLast(GL_NUM),anyLast(STD_ACCT_SEQ_NUM),DATA_DT,
        sum(ACTL_CHARGE_AMT),sum(PREFR_FEE_AMT),
        anyLast(FEE_PROD_NAME),anyLast(CHARGE_TYPE_NAME),anyLast(GL_NAME),sum(ACTL_CHARGE_AMT_DISCNT_RMB),
        sum(PREFR_FEE_AMT_DISCNT_RMB),sum(ACTL_CHARGE_VAT*SIGN),anyLast(KHLX),JLID,max(CUID),
        anyLast(ITID),anyLast(UTID),anyLast(ACID),anyLast(DIVI.DPID),anyLast(DIVI.EMID),anyLast(DIVI.DSVL),-1
        from T_CM_BIZ_FEE_SUM_M WHERE JLID IN (SELECT JLID FROM JOIN_CM_BIZ_FEE_DIVI_BACK)
        group by DATA_DT,JLID
        HAVING sum(SIGN)>0"#;
        let mut client = get_ch_pool().get_handle().await?;
        client.execute(&sql).await?;
        Ok(())
    }
    async fn insert(&self) -> anyhow::Result<()> {
        let sql = r#"insert into TMP_CM_BIZ_FEE_SUM_M_BACK(CUST_NUM,BELONG_ORG_NUM,CHARGE_TYPE_CD,FEE_PROD_ENCODE,CHAN_ID,ENTER_ACCT_CURR_CD,CASH_TRAN_IND,
		GL_NUM,STD_ACCT_SEQ_NUM,DATA_DT,ACTL_CHARGE_AMT,PREFR_FEE_AMT,FEE_PROD_NAME,CHARGE_TYPE_NAME,GL_NAME,ACTL_CHARGE_AMT_DISCNT_RMB,
		PREFR_FEE_AMT_DISCNT_RMB,ACTL_CHARGE_VAT,KHLX,JLID,CUID,ITID,UTID,ACID,DIVI.DPID,DIVI.EMID,DIVI.DSVL,SIGN)
		WITH joinGet('JOIN_CM_BIZ_FEE_DIVI_BACK','DPIDS',JLID) AS DPIDS2,joinGet('JOIN_CM_BIZ_FEE_DIVI_BACK','EMIDS',JLID) AS EMIDS2,
		joinGet('JOIN_CM_BIZ_FEE_DIVI_BACK','DSVLS',JLID) AS DSVLS2,dictGetUInt32('DICT_USER_PUBLIC','EMID',toUInt64(UTID)) AS PEMID,
		if(empty(DPIDS2),[0],DPIDS2) AS DPIDS,if(empty(EMIDS2),[0],EMIDS2) AS EMIDS,if(empty(DSVLS2),[toFloat64(AMT)],DSVLS2) AS DSVLS
		SELECT anyLast(CUST_NUM),anyLast(BELONG_ORG_NUM),anyLast(CHARGE_TYPE_CD),anyLast(FEE_PROD_ENCODE),anyLast(CHAN_ID),
        anyLast(ENTER_ACCT_CURR_CD),anyLast(CASH_TRAN_IND),anyLast(GL_NUM),anyLast(STD_ACCT_SEQ_NUM),DATA_DT,
        sum(ACTL_CHARGE_AMT),sum(PREFR_FEE_AMT),
        anyLast(FEE_PROD_NAME),anyLast(CHARGE_TYPE_NAME),anyLast(GL_NAME),sum(ACTL_CHARGE_AMT_DISCNT_RMB) AMT,
        sum(PREFR_FEE_AMT_DISCNT_RMB),sum(ACTL_CHARGE_VAT*SIGN),anyLast(KHLX),JLID,max(CUID),
        anyLast(ITID),anyLast(UTID),anyLast(ACID),DPIDS,EMIDS,DSVLS,1
        from T_CM_BIZ_FEE_SUM_M WHERE JLID IN (SELECT JLID FROM JOIN_CM_BIZ_FEE_DIVI_BACK)
        group by DATA_DT,JLID
        HAVING sum(SIGN)>0"#;
        let mut client = get_ch_pool().get_handle().await?;
        client.execute(&sql).await?;
        Ok(())
    }
    pub (super) async fn run(&self) -> anyhow::Result<()> {
        let mut client = get_ch_pool().get_handle().await?;
        let sql="truncate table JOIN_CM_BIZ_FEE_DIVI_BACK";
        client.execute(sql).await?;
        let sql = r#"INSERT INTO JOIN_CM_BIZ_FEE_DIVI_BACK
        WITH dictGetUInt32('DICT_USER_DEPT','DPID',toUInt64(EMID)) AS DPID
		select JLID,groupArray(DPID) DPIDS,groupArray(EMID) EMIDS,groupArray(DSVL) DSVLS
		from mysql.T_CM_BIZ_FEE_DIVI
		WHERE TMST>(SELECT TMST FROM mysql.CONF_UPDT)
		GROUP BY JLID"#;
        client.execute(&sql).await?;
        let sql="CREATE TABLE IF NOT EXISTS TMP_CM_BIZ_FEE_SUM_M_BACK AS T_CM_BIZ_FEE_SUM_M";
        client.execute(sql).await?;
        let sql="TRUNCATE TABLE TMP_CM_BIZ_FEE_SUM_M_BACK";
        client.execute(sql).await?;
        self.collapsing().await?;
        self.insert().await?;
        let sql="insert into T_CM_BIZ_FEE_SUM_M select * from TMP_CM_BIZ_FEE_SUM_M_BACK order by SIGN";
        client.execute(sql).await?;
        let sql="DROP TABLE TMP_CM_BIZ_FEE_SUM_M_BACK";
        client.execute(sql).await?;
        Ok(())
    }
}
